﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using Labthink.PCSoft.UniversalHost4.Model;
using Labthink.PCSoft.UniversalHost4.Model.Query;
using Labthink.PCSoft.UniversalHost4.Utility;
using NLog;

namespace Labthink.PCSoft.UniversalHost4.DataAccess
{
    public class FlowRepository
    {
        public static string AppPath = AppDomain.CurrentDomain.BaseDirectory;

        /// <summary>
        /// 主数据路路径
        /// </summary>
        public static readonly string MainDbFile = Path.Combine(AppPath, "Config\\App.db");

        private static readonly Logger Log = LogManager.GetCurrentClassLogger(); //日志

        private static void ExistDb()
        {
            if (!File.Exists(MainDbFile))
            {
                throw new FileNotFoundException($"Db file ({MainDbFile}) is not exists");
            }
        }

        /// <summary>
        /// 获取默认的参数方案
        /// </summary>
        /// <returns></returns>
        public static ParamScheme GetDefaultParamScheme()
        {
            ExistDb();
            Log.Debug("  从数据库获取默认的参数方案");
            string sql = "SELECT * FROM ParamScheme WHERE IsDefault=1 ";
            using var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile));
            con.Open();
            var cmd = con.CreateCommand();
            cmd.CommandText = sql;
            ParamScheme ps = null;
            try
            {
                IDataReader reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                if (reader.Read())
                {
                    ps = new ParamScheme
                    {
                        Id = reader["Id"].ToInt32(),
                        IsDefault = reader["IsDefault"].ToBool(),
                        IsSystemAdded = reader["IsSystemAdded"].ToBool(),
                        Scheme = reader["Scheme"].ToString(),
                        SchemeName = reader["SchemeName"].ToString(),
                        TestType = reader["TestType"].ToInt32(),
                        TestTypeName = reader["TestTypeName"].ToString()
                    };
                }
                reader.Close();
                if (ps != null)
                {
                    sql = "UPDATE ParamScheme SET UseCount=UseCount+1 WHERE Id=@Id ";
                    SqliteHelper.ExecuteNonQuery(SqliteHelper.CreateConnectionString(MainDbFile), sql, new object[] { ps.Id });
                }

                return ps;
            }
            catch (Exception e)
            {
                Log.Error("查找默认方案出错:{0}", e, sql);
                return null;
            }
        }

        /// <summary>
        /// 查找全部参数方案
        /// </summary>
        /// <returns></returns>
        public static List<ParamScheme> GetAllParamSchemes()
        {
            ExistDb();
            Log.Debug("获取参数方案列表");
            const string sql = "SELECT Id,IsDefault,IsSystemAdded,SchemeName,TestType,TestTypeName FROM ParamScheme ORDER BY testType,UseCount DESC ";
            var ps = new List<ParamScheme>();
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile)))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = sql;
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, null);
                    while (reader.Read())
                    {
                        ps.Add(new ParamScheme
                        {
                            Id = reader["Id"].ToInt32(),
                            IsDefault = reader["IsDefault"].ToBool(),
                            IsSystemAdded = reader["IsSystemAdded"].ToBool(),
                            SchemeName = reader["SchemeName"].ToString(),
                            TestType = reader["TestType"].ToInt32(),
                            TestTypeName = reader["TestTypeName"].ToString()
                        });
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error("查找全部方案出错:{0}", e, sql);
                    return null;
                }
                return ps;
            }
        }

        /// <summary>
        /// 查找全部参数方案
        /// </summary>
        /// <returns></returns>
        public static PagedList<ParamScheme> GetAllParamSchemes(QueryScheme query)
        {
            ExistDb();
            Log.Debug("获取参数方案列表");
            if (query.PageIndex < 1) query.PageIndex = 1;
            var logs = new List<ParamScheme>();
            if (query.PageIndex <= 0 || query.PageCount <= 0)
            {
                return null;
            }
            var objs = new List<object>();
            var sqlData = new StringBuilder("SELECT * FROM ParamScheme WHERE 1=1");
            var sqlCount = new StringBuilder("SELECT COUNT(1) FROM ParamScheme WHERE 1=1");
            if (!string.IsNullOrEmpty(query.Name))
            {
                sqlData.Append(" AND SchemeName LIKE @SchemeName");
                sqlCount.Append(" AND SchemeName LIKE @SchemeName");
                objs.Add("%" + query.Name + "%");
            }
            if (query.TestType.HasValue)
            {
                sqlData.Append(" AND TestType=@TestType");
                sqlCount.Append(" AND TestType =@TestType");
                objs.Add(query.TestType.Value);
            }

            sqlData.Append(" ORDER BY testType,UseCount DESC  LIMIT " + (query.PageIndex - 1) * query.PageCount + "," + query.PageCount);
            int count;
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile)))
            {
                con.Open();
                using (var cmd = con.CreateCommand())
                {
                    try
                    {
                        var reader = SqliteHelper.ExecuteReader(cmd, sqlData.ToString(), objs.ToArray());
                        while (reader.Read())
                        {
                            logs.Add(new ParamScheme
                            {
                                Id = reader["Id"].ToInt32(),
                                IsDefault = reader["IsDefault"].ToBool(),
                                IsSystemAdded = reader["IsSystemAdded"].ToBool(),
                                SchemeName = reader["SchemeName"].ToString(),
                                TestType = reader["TestType"].ToInt32(),
                                TestTypeName = reader["TestTypeName"].ToString()
                            });
                        }
                        reader.Close();
                    }
                    catch (Exception e)
                    {
                        Log.Error("查询参数方案出错:{0}", e, sqlData.ToString());
                        return null;
                    }
                }

                if (con.State != ConnectionState.Open)
                    con.Open();

                try
                {
                    count = SqliteHelper.ExecuteScalar(MainDbFile, sqlCount.ToString(), objs.ToArray()).ToInt32();
                }
                catch (Exception e)
                {
                    Log.Error("获取参数方案记录总数出错:{0}", e, sqlCount.ToString());
                    return null;
                }

                return new PagedList<ParamScheme>(logs)
                {
                    TotalCount = count,
                    PageIndex = query.PageIndex,
                    PageSize = logs.Count,
                    PageCount = (int)Math.Ceiling((double)count / query.PageCount)
                };
            }
        }

        /// <summary>
        /// 查找全部参数方案
        /// </summary>
        /// <returns></returns>
        public static List<ParamScheme> GetParamSchemes(int testType)
        {
            ExistDb();
            Log.Debug("获取参数方案列表");
            const string sql = "SELECT Id,IsDefault,IsSystemAdded,SchemeName,TestType,TestTypeName FROM ParamScheme WHERE TestType=@TestType ORDER BY UseCount DESC";
            var ps = new List<ParamScheme>();
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile)))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = sql;
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, new object[] { testType });
                    while (reader.Read())
                    {
                        ps.Add(new ParamScheme
                        {
                            Id = reader["Id"].ToInt32(),
                            IsDefault = reader["IsDefault"].ToBool(),
                            IsSystemAdded = reader["IsSystemAdded"].ToBool(),
                            SchemeName = reader["SchemeName"].ToString(),
                            TestType = reader["TestType"].ToInt32(),
                            TestTypeName = reader["TestTypeName"].ToString()
                        });
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Log.Error("查找方案出错:{0}", e, sql);
                    return null;
                }
                return ps;
            }
        }

        /// <summary>
        /// 根据ID获取方案内容
        /// 每根据ID查询一次参数方案，该方案使用次数自动加一
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ParamScheme GetParamSchemeById(int id)
        {
            ExistDb();
            Log.Debug("获取方案{0}的内容", id);
            string sql = "SELECT * FROM ParamScheme WHERE Id=@Id ";
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile)))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = sql;
                ParamScheme ps = null;
                try
                {
                    var reader = SqliteHelper.ExecuteReader(cmd, sql, new object[] { id });
                    while (reader.Read())
                    {
                        ps = new ParamScheme
                        {
                            Id = reader["Id"].ToInt32(),
                            IsDefault = reader["IsDefault"].ToBool(),
                            IsSystemAdded = reader["IsSystemAdded"].ToBool(),
                            Scheme = reader["Scheme"].ToString(),
                            SchemeName = reader["SchemeName"].ToString(),
                            TestType = reader["TestType"].ToInt32(),
                            TestTypeName = reader["TestTypeName"].ToString()
                        };
                    }

                    reader.Close();

                    sql = "UPDATE ParamScheme SET UseCount=UseCount+1 WHERE Id=@Id ";
                    SqliteHelper.ExecuteNonQuery(SqliteHelper.CreateConnectionString(MainDbFile), sql, new object[] { id });
                    return ps;
                }
                catch (Exception e)
                {
                    Log.Error("查找指定方案出错:{0}", e, sql);
                    return null;
                }
            }
        }

        /// <summary>
        /// 删除一个试验类型的所有参数方案
        /// </summary>
        /// <param name="testType"></param>
        /// <returns></returns>
        public static int DeleteParamSchemeByTestType(int testType)
        {
            ExistDb();
            Log.Debug("删除试验类型{0}的全部方案", testType);
            const string sql = "DELETE FROM ParamScheme WHERE TestType=@TestType";
            try
            {
                return SqliteHelper.ExecuteNonQuery(MainDbFile, sql, testType) >= 1 ? 0 : -1;
            }
            catch (Exception e)
            {
                Log.Error("删除试验类型{0}的参数方案出错:{1}", e, testType, sql);
                return -1;
            }
        }

        /// <summary>
        /// 删除一个参数方案
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int DeleteParamSchemeById(int id)
        {
            ExistDb();
            Log.Debug("删除编号{0}的方案", id);
            const string sql = "DELETE FROM ParamScheme WHERE Id=@Id";
            try
            {
                return SqliteHelper.ExecuteNonQuery(MainDbFile, sql, id) == 1 ? 0 : -1;
            }
            catch (Exception e)
            {
                Log.Error("删除编号{0}的参数方案出错:{1}", e, id, sql);
                return -1;
            }
        }

        /// <summary>
        /// 设置默认参数方案
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int SetDefault(int id)
        {
            ExistDb();
            Log.Debug("设置编号{0}的方案为默认", id);
            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                try
                {
                    SqliteHelper.ExecuteNonQuery(tran, "UPDATE ParamScheme SET IsDefault=0", null);
                    SqliteHelper.ExecuteNonQuery(tran, "UPDATE ParamScheme SET IsDefault=1 WHERE Id=@Id", id);
                    tran.Commit();
                    return 0;
                }
                catch (Exception e)
                {
                    Log.Error("设置编号 {0} 的方案为默认失败", e, id);
                    tran.Rollback();
                    return -1;
                }
            }
        }

        /// <summary>
        /// 更新一个参数方案
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static int UpdateParamScheme(ParamScheme ps)
        {
            ExistDb();
            Log.Debug("修改参数方案内容");

            // 未修改参数，使用原来的
            if (ps.Scheme == null)
            {
                var ps1 = GetParamSchemeById(ps.Id);
                ps.Scheme = ps1.Scheme;
            }

            using (var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile)))
            {
                con.Open();
                var tran = con.BeginTransaction();
                try
                {
                    if (ps.IsDefault)
                    {
                        SqliteHelper.ExecuteNonQuery(tran, "UPDATE ParamScheme SET IsDefault=0", null);
                    }
                    SqliteHelper.ExecuteNonQuery(tran,
                        "UPDATE ParamScheme SET TestType=@TestType,TestTypeName=@TestTypeName,SchemeName=@SchemeName,Scheme=@Scheme,IsDefault=@IsDefault WHERE Id=@Id", new object[] { ps.TestType, ps.TestTypeName, ps.SchemeName, ps.Scheme, ps.IsDefault ? 1 : 0, ps.Id });
                    tran.Commit();
                    return 0;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    Log.Error(e, "修改参数方案失败");
                    return -1;
                }
            }
        }

        private static bool Exist(string pn)
        {
            var o = SqliteHelper.ExecuteScalar(MainDbFile,
                "SELECT COUNT(1) FROM ParamScheme WHERE SchemeName=@SchemeName", pn);
            if (o == null) return false;
            return o.ToBool();
        }

        /// <summary>
        /// 增加一个参数方案
        /// </summary>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static int AddParamScheme(ParamScheme ps)
        {
            ExistDb();
            Log.Debug("增加一个参数方案");
            if (Exist(ps.SchemeName)) 
               throw new BusinessException(LanguageManager.GetLanguageValue("ActionFail"),-2);

            using var con = new SQLiteConnection(SqliteHelper.CreateConnectionString(MainDbFile));
            con.Open();
            var tran = con.BeginTransaction();
            try
            {
                if (ps.IsDefault)
                {
                    SqliteHelper.ExecuteNonQuery(tran, "UPDATE ParamScheme SET IsDefault=0", null);
                }

                var id = SqliteHelper.ExecuteScalar(tran,
                    @"INSERT INTO ParamScheme (TestType,TestTypeName,SchemeName,Scheme,IsDefault,IsSystemAdded,UseCount) 
                          VALUES (@TestType,@TestTypeName,@SchemeName,@Scheme,@IsDefault,@IsSystemAdded,1);select last_insert_rowid()", ps.TestType, ps.TestTypeName, ps.SchemeName, ps.Scheme, ps.IsDefault ? 1 : 0, ps.IsSystemAdded ? 1 : 0);
                tran.Commit();
                return id.ToInt32();
            }
            catch (Exception e)
            {
                tran.Rollback();
                Log.Error(e, "增加参数方案失败");
                throw new BusinessException(LanguageManager.GetLanguageValue("ActionFail"),-1);
            }
        }

        /// <summary>
        /// 增加一条警报
        /// </summary>
        /// <param name="alarmType">从1开始</param>
        /// <param name="alarmInfo"></param>
        /// <returns></returns>
        public static int CreateAlarm(int alarmType, string alarmInfo)
        {
            ExistDb();
            var sql = "SELECT Id FROM Alarm WHERE AlarmType=@AlarmType";
            var o = SqliteHelper.ExecuteScalar(MainDbFile, sql, alarmType);
            var id = 0;
            try
            {
                if (o == null || o.ToInt32() <= 0)
                {
                    Log.Debug("插入一条报警信息：{0}", alarmInfo);
                    //添加
                    sql = "INSERT INTO Alarm (CDate,AlarmInfo,AlarmType,AlarmStatus,AId) VALUES (@CDate,@AlarmInfo,@AlarmType,1,-1);select last_insert_rowid()";
                    id = SqliteHelper.ExecuteScalar(MainDbFile, sql, DateTime.Now, alarmInfo, alarmType).ToInt32();
                }
                else
                {
                    Log.Debug("更新一条报警信息：{0}", alarmInfo);
                    //更新
                    id = o.ToInt32();
                    sql = "UPDATE Alarm SET CDate=@CDate, AlarmInfo=@AlarmInfo WHERE Id=@Id";
                    SqliteHelper.ExecuteNonQuery(MainDbFile, sql, DateTime.Now, alarmInfo, id);
                }
            }
            catch (Exception e)
            {
                Log.Error("写入磁盘报警信息出错：{0}", e, sql);
            }
            return id;
        }

        /// <summary>
        /// 清除警报信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int CancelAlarm(int id)
        {
            Log.Debug("清除编号为{0}的报警信息", id);
            var sql = "DELETE FROM Alarm WHERE Id=@Id";
            try
            {
                return SqliteHelper.ExecuteNonQuery(MainDbFile, sql, id);
            }
            catch (Exception e)
            {
                Log.Error("清除编号为{0}的报警信息失败", e, id);
                return -1;
            }
        }
    }
}
